﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using NetWing.Common.Data.SqlServer;
using NetWing.BPM.Core.Bll;
using NetWing.BPM.Core.Model;
using NetWing.BPM.Core.Dal;
using NetWing.Common;
using NetWing.BPM.Core;

namespace NetWing.BPM.Admin.report
{
    public partial class daypt : NetWing.BPM.Core.BasePage.BpmBasePage
    {
        public DataTable fdt = null;
        public DataTable indt = null;
        public DataTable outdt = null;//当日支出dt
        public DataTable kongdt = null;//空房dt
        public int kongcount = 0;//截止当前空房统计
        public decimal dayout = 0;//当日支出
        public decimal dayin = 0;//当日收入
        protected void Page_Load(object sender, EventArgs e)
        {
            #region 当日财务
            //MJFinanceMain
            string sql = "select * from MJFinanceMain where ";
            //数据权限筛选s
            string sqlwhere = " 1=1 ";

            if (SysVisitor.Instance.cookiesIsAdmin == "False")
            { //判断是否是超管如果是超管理，所有显示
                sqlwhere = "(depid in (" + SysVisitor.Instance.cookiesDepartments + "))";//如果是超管则不显示
            }
            //数据权限筛选e
            sql = sql + sqlwhere;
            sql = sql + " and (add_time BETWEEN '" + DateTime.Now.ToString("yyyy-MM-dd 00:00:00") + "' and '" + DateTime.Now.ToString("yyyy-MM-dd 23:59:59") + "')";
            //得到当日财务dt
            fdt = SqlEasy.ExecuteDataTable(sql);
            //得到当日财务收入dt
            indt = GetNewDataTable(fdt, "total>=0", "");
            //得到当日财务收入总额
            if (indt.Rows.Count>0)
            {
                dayin = decimal.Parse(indt.Compute("sum(total)", "TRUE").ToString());
            }

            
            //得到当日支出dt
            outdt = GetNewDataTable(fdt, "total<0", "");
            if (outdt.Rows.Count>0)
            {
                dayout = decimal.Parse(outdt.Compute("sum(total)", "TRUE").ToString());
            }

            #endregion
            #region 更新账户余额
            //更新银行余额 因为这里账户id都是唯一的 这里不考虑数据权限
            SqlDataReader dr = SqlEasy.ExecuteDataReader("select * from MJBankAccount");
            while (dr.Read())
            {
                string sumsqlb = "select isnull(sum(shifub),0) shifub  from MJFinanceMain where accountidb=" + dr["Keyid"].ToString() + "";
                string sumsqlc = "select isnull(sum(shifuc),0) shifuc  from MJFinanceMain where accountidc=" + dr["Keyid"].ToString() + "";
                string sumsqld = "select isnull(sum(shifud),0) shifud  from MJFinanceMain where accountidd=" + dr["Keyid"].ToString() + "";
                decimal moneyb = (decimal)SqlEasy.ExecuteScalar(sumsqlb);
                decimal moneyc = (decimal)SqlEasy.ExecuteScalar(sumsqlc);
                decimal moneyd = (decimal)SqlEasy.ExecuteScalar(sumsqld);
                SqlEasy.ExecuteNonQuery("update MJBankAccount set accountBalance=" + (moneyb + moneyc + moneyd) + "  where keyid=" + dr["keyid"].ToString() + "");

                LogModel log = new LogModel();
                log.BusinessName = "统计银行余额-从日表表";
                log.OperationIp = PublicMethod.GetClientIP();
                log.OperationTime = DateTime.Now;
                log.PrimaryKey = "";
                log.UserId = int.Parse(SysVisitor.Instance.cookiesUserId);
                log.TableName = "";
                log.note = "账户名:" + dr["accountName"].ToString() + "depid:" + dr["depid"].ToString() + "账户ID：" + dr["keyid"].ToString() + "更新余额：" + (moneyb + moneyc + moneyd);
                log.OperationType = (int)OperationType.Other;
                LogDal.Instance.Insert(log);
            }
            #endregion
            #region 空房计算
            string kongsql = "select * from MJRooms where 1=1 ";
            kongdt = SqlEasy.ExecuteDataTable(kongsql+" and "+sqlwhere+" and state='空闲'");
            if (kongdt.Rows.Count>0)
            {
                kongcount = kongdt.Rows.Count;
            }

            #endregion

        }


        //Select();
        //Select("id>='3' and name='3--hello'");//支持and
        //Select("id>='3' or id='1'");//支持or
        //Select("name like '%hello%'");//支持like   
        //Select("id>5","id desc");
        //Select("id>5", "id desc", DataViewRowState.Added)
        //DataRow[] dr = dt.Select(“col = 'XXXX'”);//条件：就是字段名='某某'
        /// 执行DataTable中的查询返回新的DataTable
        /// </summary>
        /// <param name="dt">源数据DataTable</param>
        /// <param name="condition">查询条件</param>
        /// <returns></returns>
        private DataTable GetNewDataTable(DataTable dt, string condition, string sortstr)
        {
            DataTable newdt = new DataTable();
            newdt = dt.Clone();
            DataRow[] dr = dt.Select(condition, sortstr);
            for (int i = 0; i < dr.Length; i++)
            {
                newdt.ImportRow((DataRow)dr[i]);
            }
            return newdt;//返回的查询结果
        }
    }
}